﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using Cosmetic.DataAccess;
using Cosmetic.DataEntity;
using System.Configuration;
using System.Data.Linq.SqlClient;
using System.Data.SqlClient;
using System.Data;

namespace Cosmetic.Logic
{
    public class SaleManager : LogicBase
    {
        /// <summary>
        /// 查寻所有商品。
        /// </summary>
        /// <returns></returns>
        public static DataSet GetAllProduct()
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select ProductId ,[Name],PictureUrl,网络零售价 as [price],Views,PublishDate from  ProductInfo where ProductId in ( select ProductId from dbo.Sale )";

                SqlCommand com = new SqlCommand(cmdStr, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                return ds;
            }
            finally
            {
                con.Close();
            }
        }

        public static void DeleteSaleProduct(string ProductId)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "delete from dbo.Sale where ProductId = '" + ProductId + "'";

                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();

            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 查寻所有商品。并某列排序。
        /// </summary>
        /// <returns></returns>
        public static DataSet GetAllProductSort(string orderBy)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select ProductId ,[Name],PictureUrl,网络零售价 as [price],Views,PublishDate from  ProductInfo where ProductId in ( select ProductId from dbo.Sale )  Order by " + orderBy;

                SqlCommand com = new SqlCommand(cmdStr, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                return ds;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 查寻某分类的商品。
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public static DataSet GetProductByCategory(string categoryId)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select ProductId ,[Name],PictureUrl,网络零售价  as [price],Views,PublishDate from  ProductInfo where ProductId in ( select ProductId from dbo.Sale  where CatetoryId = '" + categoryId + "')";

                SqlCommand com = new SqlCommand(cmdStr, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                return ds;
            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>
        /// 查寻某分类的商品。 按某列排序。
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public static DataSet GetProductByCategorySort(string categoryId, string sort)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select ProductId ,[Name],PictureUrl,网络零售价  as [price],Views,PublishDate from  ProductInfo where ProductId in ( select ProductId from dbo.Sale  where CatetoryId = '" + categoryId + "') order by " + sort;

                SqlCommand com = new SqlCommand(cmdStr, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                return ds;
            }
            finally
            {
                con.Close();
            }
        }

        public static void AddProductToSale(string ProductId, string categoryId)
        {
            ProductManager m_ProductManager = new ProductManager();

            Product pdt = m_ProductManager.GetProductById(new Guid(ProductId));
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "insert into Sale(ProductId, CatetoryId, Sequence, CreateTime) values('" + pdt.ProductId.ToString() + "','" + categoryId + "','0','" + DateTime.Now.ToString() + "'" + ")";


                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();

                //DataSet ds = new DataSet();
                //SqlDataAdapter adap = new SqlDataAdapter(com);
                //adap.Fill(ds);
                //return ds;
            }
            finally
            {
                con.Close();
            }

            //ProductManager m_ProductManager = new ProductManager();

            //Product pdt = m_ProductManager.GetProductById(new Guid(ProductId));

            //Category twoUpperCategory = pdt.ProductCategory.SupCategory.SupCategory;
            //Category threeUpperCategory = pdt.ProductCategory.SupCategory.SupCategory.SupCategory;

            //if (twoUpperCategory.CategoryId == twoUpperCategory.SupCategoryId)
            //{
            //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            //    SqlConnection con = new SqlConnection(conStr);
            //    try
            //    {
            //        con.Open();
            //        string cmdStr = "insert into Sale(ProductId, CatetoryId, Sequence, CreateTime) values('" + pdt.ProductId.ToString() + "','" + categoryId + "','0','" + DateTime.Now.ToString() + "'" + ")";


            //        SqlCommand com = new SqlCommand(cmdStr, con);
            //        com.ExecuteNonQuery();

            //        //DataSet ds = new DataSet();
            //        //SqlDataAdapter adap = new SqlDataAdapter(com);
            //        //adap.Fill(ds);
            //        //return ds;
            //    }
            //    finally
            //    {
            //        con.Close();
            //    }
            //}
            //else if (threeUpperCategory.CategoryId == threeUpperCategory.SupCategoryId)
            //{
            //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            //    SqlConnection con = new SqlConnection(conStr);
            //    try
            //    {
            //        con.Open();
            //        string cmdStr = "insert into Sale(ProductId, CatetoryId, Sequence, CreateTime) values('" + pdt.ProductId.ToString() + "','" + threeUpperCategory.CategoryId.ToString() + "','0','" + DateTime.Now.ToString() + "'" + ")";


            //        SqlCommand com = new SqlCommand(cmdStr, con);
            //        com.ExecuteNonQuery();

            //        //DataSet ds = new DataSet();
            //        //SqlDataAdapter adap = new SqlDataAdapter(com);
            //        //adap.Fill(ds);
            //        //return ds;
            //    }
            //    finally
            //    {
            //        con.Close();
            //    }
            //}
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        public bool CheckSaleProduct(string productId)
        {
            List<Sale> result = (from sale in m_CosmeticDC.Sales
                                 where sale.ProductId == new Guid(productId)
                                 select sale).ToList();


            if (result.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public decimal GetDiscountRatio()
        {
            var result = (from saleratio in m_CosmeticDC.SaleRatios
                          select saleratio).First();
            return result.ratio.Value;

        }


        public decimal GetDiscountRatiof(string name)
        {
            var result = (from saleratio in m_CosmeticDC.zheku
                          where saleratio.namejibie == name
                          select saleratio).First();
            return result.rate.Value;

        }
        public decimal GetDisCountRatio1()
        {
            var result = (from saleratio in m_CosmeticDC.SaleRatios
                          select saleratio).First();
            return result.ratio1.Value;
        }


        public void Insertzheku(zheku anOrder)
        {
            m_CosmeticDC.zheku.InsertOnSubmit(anOrder);
            m_CosmeticDC.SubmitChanges();
        }
        public void DeleteOrder(zheku anOrder)
        {
            m_CosmeticDC.zheku.DeleteOnSubmit(anOrder);
            m_CosmeticDC.SubmitChanges();
        }

        public List<zheku> GetAll()
        {
            return (from theActivity in m_CosmeticDC.zheku
                    select theActivity).ToList();
        }

        public zheku GetProductById(int aProductId)
        {
            return m_CosmeticDC.zheku.Single(theProect => theProect.id == aProductId);
        }

        public youhui getyouhui(string number)
        {
            return m_CosmeticDC.youhui.Single(theProect => theProect.number == number);
        }
        public void Deleteyouhui(youhui  anumber)
        {
            m_CosmeticDC.youhui .DeleteOnSubmit(anumber);
            m_CosmeticDC.SubmitChanges();
        }

        public IEnumerable GetAllyouhui()
        {
            var result = from theFocus in m_CosmeticDC.youhui 
                         join theProduct in m_CosmeticDC.Users
                         on theFocus.userid  equals theProduct.UserId
                         select new
                         {
                             id = theFocus .id ,
                             tel = theProduct.tel ,
                             number=theFocus.number,
                             date= theFocus .date 

                         };
            return result;
        }

        public youhui GetyouhuiById(int aProductId)
        {
            return m_CosmeticDC.youhui.Single(theProect => theProect.id  == aProductId);
        }

        public decimal GetDiscountRatiof1(string number)
        {
            var result = (from saleratio in m_CosmeticDC.youhui
                          where saleratio.number == number
                          select saleratio).First();
            return result.rate.Value;

        }


        public youhui CheckExistes(string uername)
        {
            List<youhui> theProductList = (from theProduct in m_CosmeticDC.youhui
                                           where theProduct.number == uername
                                           select theProduct).ToList();
            if (theProductList.Count == 1)
            {
                return theProductList[0];
            }
            return null;
        }

    }//end 
}
